﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.ComponentModel;
using QuanLyRapChieuPhim.DTO;
using QuanLyRapChieuPhim.BUS;
using System.IO;
using System.Data;

namespace QuanLyRapChieuPhim.DAO
{
    class DataProvider_Excel
    {
        
        public static string FileName = "ExcelFile.xls";

        //Khai báo biến 
        private String m_strConnectionString  = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                   "Data Source=" + FileName + ";" + 
                   "Extended Properties='Excel 8.0;HDR=YES'";

        private OleDbConnection m_Connection ;
        private String m_error ;

        public DataProvider_Excel()
        { 
            
        }

        public DataProvider_Excel(string _ConnectionString)
        { 
            m_strConnectionString = _ConnectionString;
            m_Connection = new OleDbConnection(_ConnectionString);
        }

        public void Ex_KhachHang()
        {
            if (File.Exists(FileName))
                File.Delete(FileName);

            m_Connection = new OleDbConnection();
            m_Connection.ConnectionString = m_strConnectionString;
            m_Connection.Open();
            OleDbCommand cmd = new OleDbCommand();
            cmd.Connection = m_Connection;

            cmd.CommandText = "CREATE TABLE KhachHang(MaKhachHang int,TenKhachHang nvarchar(50),CMND varchar(10)," +
                "NgaySinh datetime,GioiTinh nvarchar(4),DiaChi nvarchar(100),DienThoai varchar(12),DaXoa int)";
            cmd.ExecuteNonQuery();

            Insert_KhachHang();

            m_Connection.Close();
        }

        public void Ex_NhanVien()
        { 
            
            if (File.Exists(FileName))
                File.Delete(FileName);

            m_Connection = new OleDbConnection();
            m_Connection.ConnectionString = m_strConnectionString;
            m_Connection.Open();
            OleDbCommand cmd = new OleDbCommand();
            cmd.Connection = m_Connection;

            cmd.CommandText = "CREATE TABLE NhanVien(MaNhanVien int,TenNhanVien nvarchar(50),CMND varchar(10)," +
                "NgaySinh datetime,GioiTinh nvarchar(4),DiaChi nvarchar(100),DienThoai varchar(12),MaChucVu int,TenChucVu nvarchar(50),DaXoa int)";
            cmd.ExecuteNonQuery();

            Insert_NhanVien();

            m_Connection.Close();
        }

        public bool Import_KhachHang()
        {
            BindingList<KhachHangDTO> ds = GetList_KhachHang();
            foreach (KhachHangDTO DTO in ds)
            {
                try
                {
                    KhachHangBUS BUS = new KhachHangBUS();
                    if (!BUS.IsExists(DTO))
                        BUS.Add_Import(DTO);
                }
                catch (Exception)
                {
                    return false;
                }
            }
            return true;
        }

        public bool Import_NhanVien()
        {
            BindingList<NhanVienDTO> ds = GetList_NhanVien();
            foreach (NhanVienDTO DTO in ds)
            {
                try
                {
                    NhanVienBUS BUS = new NhanVienBUS();
                    if (!BUS.IsExists(DTO))
                        BUS.Add_Import(DTO);
                }
                catch (Exception)
                {
                    return false;
                }
            }
            return true;
        }

        public void Insert_KhachHang()
        {
            KhachHangBUS BUS = new KhachHangBUS();
            BindingList<KhachHangDTO> List = BUS.GetListExport();
            foreach (KhachHangDTO DTO in List)
            {
                OleDbCommand cmd = new OleDbCommand();
                cmd.Connection = m_Connection;

                cmd.CommandText = "INSERT INTO KhachHang(MaKhachHang,TenKhachHang,CMND," +
                "NgaySinh,GioiTinh,DiaChi,DienThoai,DaXoa)" +
                " Values (" +
                DTO.MaKhachHang + ",'" + DTO.TenKhachHang + "','" + DTO.CMND + "','" + DTO.NgaySinh +
                "','" + DTO.GioiTinh + "','" + DTO.DiaChi + "','" +
                DTO.DienThoai + "'," + DTO.DaXoa + ")";

                cmd.ExecuteNonQuery();
            }
        }

        public void Insert_NhanVien()
        {
            NhanVienBUS BUS = new NhanVienBUS();
            BindingList<NhanVienDTO> List = BUS.GetListExport();
            foreach (NhanVienDTO DTO  in List)
            {
                OleDbCommand cmd = new OleDbCommand();
                cmd.Connection = m_Connection;

                cmd.CommandText = "INSERT INTO NhanVien(MaNhanVien,TenNhanVien,CMND," +
                "NgaySinh,GioiTinh,DiaChi,DienThoai,MaChucVu,TenChucVu,DaXoa)" +
                " Values (" +
                DTO.MaNhanVien + ",'" + DTO.TenNhanVien + "','" + DTO.CMND + "','" + DTO.NgaySinh +
                "','" + DTO.GioiTinh + "','" + DTO.DiaChi + "','" +
                DTO.DienThoai + "'," + DTO.MaChucVu + ",'" +
                DTO.TenChucVu + "'," + DTO.DaXoa + ")";

                cmd.ExecuteNonQuery();
            }
        }

        public BindingList<KhachHangDTO> GetList_KhachHang()
        {
            try
            {
                m_Connection = new OleDbConnection();
                m_Connection.ConnectionString = m_strConnectionString;
                m_Connection.Open();
                OleDbCommand cmd = new OleDbCommand();
                cmd.Connection = m_Connection;
                cmd.CommandText = "select * from KhachHang";
                OleDbDataAdapter dbadapter = new OleDbDataAdapter(cmd);
                DataTable dt = new DataTable();
                dbadapter.Fill(dt);

                BindingList<KhachHangDTO> ds = new BindingList<KhachHangDTO>();
                foreach (DataRow row in dt.Rows)
                {
                    KhachHangDTO KhachHang = new KhachHangDTO();
                    KhachHang.MaKhachHang = int.Parse(row["maKhachHang"].ToString());
                    KhachHang.TenKhachHang = row["tenKhachHang"].ToString();
                    KhachHang.CMND = row["CMND"].ToString();
                    KhachHang.NgaySinh = DateTime.Parse(row["ngaySinh"].ToString());
                    KhachHang.GioiTinh = (row["gioiTinh"].ToString() == "True") ? "Nam" : "Nữ";
                    KhachHang.DiaChi = row["diaChi"].ToString();
                    KhachHang.DienThoai = row["dienThoai"].ToString();
                    KhachHang.DaXoa = int.Parse(row["DaXoa"].ToString());
                    ds.Add(KhachHang);
                }
                return ds;
            }
            catch (Exception)
            {
                return null;
            }
            finally
            {
                m_Connection.Close();
            }
        }

        public BindingList<NhanVienDTO> GetList_NhanVien()
        {
            try
            {
                m_Connection = new OleDbConnection();
                m_Connection.ConnectionString = m_strConnectionString;
                m_Connection.Open();
                OleDbCommand cmd = new OleDbCommand();
                cmd.Connection = m_Connection;
                cmd.CommandText = "select * from NhanVien";
                OleDbDataAdapter dbadapter = new OleDbDataAdapter(cmd);
                DataTable dt = new DataTable();
                dbadapter.Fill(dt);

                BindingList<NhanVienDTO> ds = new BindingList<NhanVienDTO>();
                foreach (DataRow row in dt.Rows)
                {
                    NhanVienDTO NhanVien = new NhanVienDTO();
                    NhanVien.MaNhanVien = int.Parse(row["maNhanVien"].ToString());
                    NhanVien.TenNhanVien = row["tenNhanVien"].ToString();
                    NhanVien.CMND = row["CMND"].ToString();
                    NhanVien.NgaySinh = DateTime.Parse(row["ngaySinh"].ToString());
                    NhanVien.GioiTinh = (row["gioiTinh"].ToString() == "True") ? "Nam" : "Nữ";
                    NhanVien.DiaChi = row["diaChi"].ToString();
                    NhanVien.DienThoai = row["dienThoai"].ToString();
                    NhanVien.MaChucVu = int.Parse(row["maChucVu"].ToString());
                    NhanVien.DaXoa = int.Parse(row["DaXoa"].ToString());
                    ds.Add(NhanVien);
                }
                return ds;
            }
            catch (Exception)
            {
                return null;
            }
            finally
            {
                m_Connection.Close();
            }
        }

    }
}
